﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using BCNPCObject.DB;
using DbLinq;
using DbLinq.Oracle;
using CMISLibrary.Entities;
using CMISLibrary;

namespace BCNPCObject.EXCEL
{
    public class cls_DV_YCAU_KNAI_Controller
    {
        #region   Atributes

        //private DataSet CMIS_Header = new CMISOutputParameter();
        private DV_YCAU_KNAI info;

        #endregion

        #region   Constructor

        public cls_DV_YCAU_KNAI_Controller()
        {
            info = new DV_YCAU_KNAI();
        }
        public cls_DV_YCAU_KNAI_Controller(DV_YCAU_KNAI Info)
        {
            this.info = Info;
        }

        #endregion

        #region   Properties

        public DV_YCAU_KNAI pInfor
        {
            get { return info; }
            set { info = value; }
        }

        #endregion

        #region   Methods
        public bool checkdonvidiachinh(string ma_dviqly, string ma_diachinh)
        {
            try
            {
                
                var dichinh = (from ord in CMIS2.DB.D_DVI_DCHINH
                              where ord.MA_DVIDCHINH == ma_diachinh
                              select ord).ToList();
                if (dichinh == null || dichinh.Take(1).Count() <= 0)
                {
                    return false;
                }
                D_DVI_DCHINH objD_DVI_DCHINH = CMIS2.DB.D_DVI_DCHINH.Single(c => c.MA_DVIDCHINH == ma_diachinh);
                var dv = (from ord in CMIS2.DB.D_DVI_QLY
                          where ord.MA_DVIQLY == ma_dviqly && ord.ID_DIA_CHINH == objD_DVI_DCHINH.ID_DIA_CHINH
                          select ord).ToList();
                if (dv == null && dv.Take(1).Count() <= 0)
                {
                    return false;
                }
                return true;
            }
            catch
            { return false; }
        }
        public bool checktram(string ma_dviqly, string ma_tram)
        {
            try
            {

                var tram = (from ord in CMIS2.DB.D_TRAM
                            where ord.MA_DVIQLY == ma_dviqly && ord.MA_TRAM == ma_tram && ord.TINH_TRANG == 1
                               select ord).ToList();
                if (tram == null || tram.Take(1).Count() <= 0)
                {
                    return false;
                }
                return true;
            }
            catch
            { return false; }
        }
        public bool insert_DV_YCAU_KNAI(ref long id_MaYeuCau ,ref string maloi)
        {
            long _ID = CMIS2.DB.ExecuteCommand("Select SEQ_DV_YCAU_KNAI.NEXTVAL from dual");
            if (_ID == -1) return false;
            try
            {
                info.MA_YCAU_KNAI = Convert.ToInt64(_ID);
                DV_YCAU_KNAI objDV_YCAU_KNAI = new DV_YCAU_KNAI()
                {
                    MA_DVIQLY = info.MA_DVIQLY,
                    MA_YCAU_KNAI = info.MA_YCAU_KNAI,
                    SO_PHIEU_YCAU = info.SO_PHIEU_YCAU,
                    ID_VITRI_HSO = info.ID_VITRI_HSO,
                    MA_KHANG_YCAU = info.MA_KHANG_YCAU,
                    MA_KHANG = info.MA_KHANG,
                    TEN_NGUOIYCAU = info.TEN_NGUOIYCAU,
                    DCHI_NGUOIYCAU = info.DCHI_NGUOIYCAU,
                    NGAY_TNHAN = info.NGAY_TNHAN,
                    NOI_DUNG_YCAU = info.NOI_DUNG_YCAU,
                    NGAY_HTHANH = info.NGAY_HTHANH,
                    MA_TCHAT = info.MA_TCHAT,
                    MA_HTHUC = info.MA_HTHUC,
                    MA_LOAI_YCAU = info.MA_LOAI_YCAU,
                    TINH_TRANG = info.TINH_TRANG,
                    DTHOAI = info.DTHOAI,
                    MA_CNANG = info.MA_CNANG,
                    NGAY_TAO = info.NGAY_TAO,
                    NGUOI_TAO = info.NGUOI_TAO,
                    NGAY_SUA = info.NGAY_SUA,
                    NGUOI_SUA = info.NGUOI_SUA
                };
                CMIS2.DB.DV_YCAU_KNAI.InsertOnSubmit(info);
                CMIS2.DB.SubmitChanges();
                id_MaYeuCau = _ID;
                CMIS2.ResetDB();
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        public bool delete_DV_YCAU_KNAI(DataTable dt, ref string maloi)
        {
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        var query = from p in CMIS2.DB.DV_YCAU_KNAI
                                    where (p.MA_DVIQLY == dt.Rows[i]["MA_DVIQLY"].ToString() && p.MA_YCAU_KNAI == Convert.ToInt64(dt.Rows[i]["MA_YCAU_KNAI"]))
                                    select p;
                        if (query != null && query.Count() > 0)
                        {
                            CMIS2.DB.DV_YCAU_KNAI.DeleteAllOnSubmit(query.ToList());
                            CMIS2.DB.SubmitChanges();
                        }
                    }
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB(); return false;
            }
        }


        // lấy thông tin mã khách hàng và mã hợp đồng
        public DataSet select_MATUSINH(string ma_dviqly,string kieu_ma)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                var x = (from a in CMIS2.DB.HDG_MATUSINH.ToList()
                         where a.MA_DVIQLY == ma_dviqly && a.KIEU_MA == kieu_ma && a.TRANG_THAI == 0 // trang thai = 0 lấy khách hàng sinh hoạt
                         select a).ToList();
                if (x != null && x.Take(1).Count() > 0)
                {
                    HDG_MATUSINH obj = x.Single(b => b.MA_TU_SINH == x.Max(c => c.MA_TU_SINH));
                    var result = from a in x
                                 where a.MA_DVIQLY == obj.MA_DVIQLY && a.ID_HDG_MATUSINH == obj.ID_HDG_MATUSINH
                                 select new
                                 {
                                     a.MA_DVIQLY,
                                     a.ID_HDG_MATUSINH,
                                     a.KIEU_MA,
                                     a.MA_TIEN_TO,
                                     a.MA_TU_SINH,
                                     a.TRANG_THAI,
                                 };
                    dt = Utility.LINQToDataTable(result);
                    ds.Tables.Add(dt);
                }
                return ds;
            }
            catch (Exception ex)
            {
                //maloi = ex.ToString();
                CMIS2.ResetDB(); return null;
            }
        }
        public bool update_MATUSINH(string ma_dviqly, Int64 ID_HDG_MATUSINH,long ma_tu_sinh)
        {
            try
            {
                var query = from ord in CMIS2.DB.HDG_MATUSINH
                            where ord.MA_DVIQLY == ma_dviqly && ord.ID_HDG_MATUSINH == ID_HDG_MATUSINH
                            select ord;

                foreach (HDG_MATUSINH ord in query)
                {
                    ord.MA_TU_SINH = ma_tu_sinh;
                }

                // Submit the changes to the database.
                CMIS2.DB.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                //strErr = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        #endregion
    }
}
